package com.boesch.electroOffice.dataaccess.daoImpl.messerli;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

import com.boesch.electroOffice.dataaccess.common.IllegalDataAccessException;
import com.boesch.electroOffice.dataaccess.common.ReturnCodesDataaccess;
import com.boesch.electroOffice.dataaccess.dao.messerli.CustomerDao;
import com.boesch.electroOffice.dataaccess.mapper.messerli.CustomerMesserliMapper;
import com.boesch.electroOffice.model.messerli.Customer;
import com.boesch.electroOffice.utils.DateUtils;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.model.User;

/**
 * This class executes the sql statements on the messerli database for class Customer.
 * If a Exception is thrown, 
 * it will be unpacked to a illegalDataAccessException 
 * including a return code which can be shown to the user.
 * 
 * @author Ralph Koster
 * 
 */
@Repository
public class CustomerDaoImpl implements CustomerDao, Serializable {

	private static final long serialVersionUID = 6528100134346214192L;
	private static final Log log = LogFactoryUtil.getLog(CustomerDaoImpl.class);

	@Autowired
	private JdbcTemplate maJdbcTemplate;
	@Autowired
	private TransactionTemplate maTransactionTemplate;

	@Value("${messerli.queryCreateCustomer}")
	private String queryCreateCustomer;
	@Value("${messerli.queryReadCustomerByCustomerId}")
	private String queryReadCustomerByCustomerId;
	@Value("${messerli.queryReadCustomerByEmail}")
	private String queryReadCustomerByEmail;
	@Value("${messerli.queryReadCustomersByName}")
	private String queryReadCustomersByName;
	@Value("${messerli.queryReadAllCustomers}")
	private String queryReadAllCustomers;
	@Value("${messerli.queryReadAllSalutations}")
	private String queryReadAllSalutations;
	@Value("${messerli.queryUpdateCustomer}")
	private String queryUpdateCustomer;
	@Value("${messerli.queryGetLastCustomerNumber}")
	private String queryGetLastCustomerNumber;

	/**
	 * {@inheritDoc}
	 */
	public String saveCustomerOnDatabase(final Customer customer, final User currentUser) throws IllegalDataAccessException {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			maTransactionTemplate.execute(new TransactionCallbackWithoutResult() {
				@Override
				public void doInTransactionWithoutResult(TransactionStatus status) {
					maJdbcTemplate.update(new PreparedStatementCreator() {
						public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
							PreparedStatement ps = connection.prepareStatement(queryCreateCustomer, Statement.RETURN_GENERATED_KEYS);
							ps.setInt(1, getLastCustomerNumber() + 1);
							ps.setString(2, customer.getSalutation());
							ps.setString(3, customer.getLastname());
							ps.setString(4, customer.getFirstname());
							ps.setString(5, customer.getPoBox());
							ps.setString(6, customer.getStreet());
							ps.setString(7, customer.getCity());
							ps.setString(8, customer.getPlz());
							ps.setString(9, customer.getCountry());
							ps.setString(10, customer.getTelPrivate());
							ps.setString(11, customer.getTelBusiness());
							ps.setString(12, customer.getFax());
							ps.setString(13, customer.getMobile());
							ps.setString(14, customer.getEmail());
							ps.setString(15, customer.getHomepage());
							ps.setString(16, "rko");
							ps.setTimestamp(17, DateUtils.getCurrentTimestamp());
							return ps;
						}

						private int getLastCustomerNumber() {
							return maJdbcTemplate.queryForObject(queryGetLastCustomerNumber, Integer.class);
						}
					}, keyHolder);
				}
			});
			log.debug("Inserted role with id: " + keyHolder.getKey());
			return ReturnCodesDataaccess.CREATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.CREATION_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public Customer readCustomerByCustomerId(int customerId) throws IllegalDataAccessException {
		Customer customer = null;
		List<Customer> customers = maJdbcTemplate.query(queryReadCustomerByCustomerId, new Object[] { customerId }, new CustomerMesserliMapper());
		if (null != customers && customers.size() == 1) {
			customer = customers.get(0);
			return customer;
		} else {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.OBJECT_NOT_FOUND_ERROR.getValue());
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public Customer readCustomerByEmailAddress(String emailAddress) throws IllegalDataAccessException {
		Customer customer = null;
		List<Customer> customers = maJdbcTemplate.query(queryReadCustomerByEmail, new Object[] { emailAddress }, new CustomerMesserliMapper());
		if (null != customers && customers.size() == 1) {
			customer = customers.get(0);
			return customer;
		} else {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.OBJECT_NOT_FOUND_ERROR.getValue());
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<Customer> readCustomersByName(String firstname, String lastname) throws IllegalDataAccessException {
		List<Customer> customers = new ArrayList<Customer>();
		try {
			customers = maJdbcTemplate.query(queryReadCustomersByName, new Object[] { lastname, firstname }, new CustomerMesserliMapper());
			return customers;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<Customer> readAllCustomers() throws IllegalDataAccessException {
		List<Customer> customers = new ArrayList<Customer>();
		try {
			customers = maJdbcTemplate.query(queryReadAllCustomers, new CustomerMesserliMapper());
			return customers;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public List<String> readAllSalutations() throws IllegalDataAccessException {
		try {
			List<String> salutations = maJdbcTemplate.query(queryReadAllSalutations, new RowMapper<String>() {
				public String mapRow(ResultSet rs, int rowNum) throws SQLException {
					return rs.getString(1);
				}
			});
			return salutations;
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.NO_OBJECTS_FOUND_ERROR.getValue(), e);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	public String updateCustomerOnDatabase(final Customer customer, final User currentUser) throws IllegalDataAccessException {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			maJdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(queryUpdateCustomer, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, customer.getSalutation());
					ps.setString(2, customer.getLastname());
					ps.setString(3, customer.getFirstname());
					ps.setString(4, customer.getPoBox());
					ps.setString(5, customer.getStreet());
					ps.setString(6, customer.getCity());
					ps.setString(7, customer.getPlz());
					ps.setString(8, customer.getCountry());
					ps.setString(9, customer.getTelPrivate());
					ps.setString(10, customer.getTelBusiness());
					ps.setString(11, customer.getFax());
					ps.setString(12, customer.getMobile());
					ps.setString(13, customer.getEmail());
					ps.setString(14, customer.getHomepage());
					ps.setString(15, "rko");
					ps.setTimestamp(16, DateUtils.getCurrentTimestamp());
					ps.setInt(17, customer.getCustomerId());
					return ps;
				}
			}, keyHolder);
			log.debug("Updated customer with id: " + customer.getCustomerId());
			return ReturnCodesDataaccess.UPDATED.getValue();
		} catch (DataAccessException e) {
			throw new IllegalDataAccessException(ReturnCodesDataaccess.UPDATE_ERROR.getValue(), e);
		}
	}
}
